// This File is part of the "nyaqt" Project
// 
// Copyright  2009 sometmes@gmail.com
// All rights reserved
// 
// This library is free software; you can redistribute it and/or modify
// it under the terms of the GNU Lesser General Public License as published
// by the Free Software Foundation; either version 2.1 of the License, or
// (at your option) any later version.
// This program is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU General Public License for more details.
// 
// You should have received a copy of the GNU General Public License
// along with this program.  If not, see <http://www.gnu.org/licenses/>.



using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using nyaqt.Plugins;
using System.Globalization;
using System.Collections.Specialized;
using System.Data.Common;
using System.Text.RegularExpressions;
using System.IO;

namespace nyaqt
{
    public class Type1ConnectionProperties
    {
        protected IDbConnection _cn = null;

        public event ConnectionMessageDelegate ConnectionMessage;

        protected void FireConnectionMessage(string text)
        {
            if (ConnectionMessage != null)
            {
                ConnectionMessage.Invoke(text);
            }
        }

        protected IConnectionInfo _connectionInfo;
        public IConnectionInfo ConnectionInfo
        {
            get { return _connectionInfo; }
            set { _connectionInfo = value; }
        }

        public string Database
        {
            get
            {
                return this._cn.Database;
            }
        }

        public string UserName
        {
            get
            {
                return _connectionInfo.ConnectionStringBuilder.Login;

            }
        }

        public string LineCommentString
        {
            get { return "--"; }
        }

        public string FormatSelectTopQuery(int top, DatabaseEntityInfo table, List<DatabaseEntityInfo> columns)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT TOP ").AppendLine(top.ToString());
            bool first = true;
            foreach (DatabaseEntityInfo col in columns)
            {
                sql.Append("\t");
                if (!first) sql.Append(",");
                sql.Append("t.[").Append(col.ColumnName).AppendLine("]");
                first = false;
            }
            sql.Append("FROM\t").Append("[").Append(table.TableNamespace).Append("].[").Append(table.TableName).AppendLine("] t");
            sql.AppendLine("--WHERE");
            first = true;
            foreach (DatabaseEntityInfo col in columns)
            {
                sql.Append("--");
                sql.Append("\t");
                if (!first) sql.Append("AND ");
                sql.Append("t.[").Append(col.ColumnName).AppendLine("]");
                first = false;
            }
            return sql.ToString();
        }

        public string FormatUpdateScript(Dictionary<string, object> rowOriginal, Dictionary<string, object> rowCurrent, List<string> cellsSelected, List<DatabaseEntityInfo> tables, List<DatabaseEntityInfo> columns)
        {
            StringBuilder sql = new StringBuilder();
            bool first = true;
            if (tables.Count == 0)
            {
                sql.Append("UPDATE 0_table_name").AppendLine(" ");
            }
            foreach (DatabaseEntityInfo table in tables)
            {
                if (!first) sql.Append(this.LineCommentString);
                sql.Append("UPDATE ");
                if (!string.IsNullOrEmpty(table.TableNamespace))
                    sql.AppendFormat("[{0}].", table.TableNamespace);
                sql.AppendFormat("[{0}]", table.TableName).AppendLine(" ");
                first = false;
            }
            first = true;
            foreach (DatabaseEntityInfo col in columns)
            {
                if (rowCurrent == null || cellsSelected == null || cellsSelected.Contains(col.ColumnName))
                {
                    if (first)
                        sql.Append("   SET ");
                    else
                        sql.Append("      ,");
                    sql.Append("[").Append(col.ColumnName).Append("] = ");
                    if (rowCurrent == null)
                        sql.AppendLine(FlattenColumnInfo(col));
                    else
                        sql.AppendLine(FormatDbValue(rowCurrent[col.ColumnName]));
                    first = false;
                }
            }
            sql.AppendLine("WHERE");
            StringBuilder withkeys = new StringBuilder();
            StringBuilder nokeys = new StringBuilder();
            first = true;
            foreach (DatabaseEntityInfo col in columns)
            {
                StringBuilder onecol = new StringBuilder();
                if (first)
                    onecol.Append("      ");
                else
                    onecol.Append("  AND ");
                onecol.Append("[").Append(col.ColumnName).Append("] = ");
                if (rowOriginal == null)
                    onecol.AppendLine(FlattenColumnInfo(col));
                else
                    onecol.AppendLine(FormatDbValue(rowOriginal[col.ColumnName]));

                nokeys.Append(onecol);
                if (col.IsPrimaryKey)
                    withkeys.Append(onecol);
                first = false;
            }
            if (withkeys.Length > 0)
                sql.Append(withkeys);
            else
                sql.Append(nokeys);
            sql.AppendLine();
            return sql.ToString();
        }

        public string FormatInsertScript(Dictionary<string, object> row, List<DatabaseEntityInfo> tables, List<DatabaseEntityInfo> columns)
        {
            StringBuilder sql = new StringBuilder();
            bool first = true;
            if (tables.Count == 0)
            {
                sql.Append("INSERT 0_table_name").AppendLine(" (");
            }
            foreach (DatabaseEntityInfo table in tables)
            {
                if (!first) sql.Append(this.LineCommentString);
                sql.Append("INSERT ");
                if (!string.IsNullOrEmpty( table.TableNamespace))
                    sql.AppendFormat("[{0}].", table.TableNamespace);
                sql.AppendFormat("[{0}]", table.TableName).AppendLine(" (");
                first = false;
            }
            first = true;
            foreach (DatabaseEntityInfo col in columns)
            {
                if (col.IsNullable && row == null)
                    sql.Append(this.LineCommentString);
                sql.Append("\t");
                if (!first) sql.Append(",");
                sql.Append("[").Append(col.ColumnName).AppendLine("]");
                first = false;
            }
            sql.AppendLine(") VALUES (");
            first = true;
            foreach (DatabaseEntityInfo col in columns)
            {
                if (col.IsNullable && row == null)
                    sql.Append(this.LineCommentString);
                sql.Append("\t");
                if (!first) sql.Append(",");
                if (row == null)
                    sql.AppendLine(FlattenColumnInfo(col));
                else
                    sql.Append(FormatDbValue(row[col.ColumnName])).Append("  ").Append(this.LineCommentString).AppendLine(col.ColumnName); //.AppendLine(FlattenColumnInfo(col));
                first = false;
            }
            sql.AppendLine(")");
            return sql.ToString();
        }

        public string FormatDeleteScript(Dictionary<string, object> rowOriginal, List<DatabaseEntityInfo> tables, List<DatabaseEntityInfo> columns)
        {
            StringBuilder sql = new StringBuilder();
            bool first = true;
            if (tables.Count == 0)
            {
                sql.AppendLine("DELETE 0_table_name");
            }
            foreach (DatabaseEntityInfo table in tables)
            {
                if (!first) sql.Append(this.LineCommentString);
                sql.Append("DELETE ");
                if (!string.IsNullOrEmpty(table.TableNamespace))
                    sql.AppendFormat("[{0}].", table.TableNamespace);
                sql.AppendFormat("[{0}]", table.TableName).AppendLine(" ");
                first = false;
            }
            sql.AppendLine("WHERE");
            StringBuilder withkeys = new StringBuilder();
            StringBuilder nokeys = new StringBuilder();
            first = true;
            foreach (DatabaseEntityInfo col in columns)
            {
                StringBuilder onecol = new StringBuilder();
                if (first)
                    onecol.Append("      ");
                else
                    onecol.Append("  AND ");
                onecol.Append("[").Append(col.ColumnName).Append("] = ");
                if (rowOriginal == null)
                    onecol.AppendLine(FlattenColumnInfo(col));
                else
                    onecol.AppendLine(FormatDbValue(rowOriginal[col.ColumnName]));

                nokeys.Append(onecol);
                if (col.IsPrimaryKey)
                    withkeys.Append(onecol);
                first = false;
            }
            if (withkeys.Length > 0)
                sql.Append(withkeys);
            else
                sql.Append(nokeys);
            sql.AppendLine();
            return sql.ToString();
        }

        private string FlattenColumnInfo(DatabaseEntityInfo col)
        {
            //return col.DisplayName.Replace("(", "").Replace(".", "_").Replace(" ", "").Replace(":", "_").Replace(",", "_").Replace(")", "");
            string[] parts = col.DisplayName.Split(new char[] { '(', ')', ' ', ':', '.', ',' }, StringSplitOptions.RemoveEmptyEntries);
            return string.Join("_", parts);
        }

        protected string FormatDbValue(object val)
        {
            if (val == null || val == DBNull.Value)
                return "NULL";
            else if (val is DateTime)
            {
                DateTime dval = (DateTime)val;
                if (dval == dval.Date)
                    return string.Format("'{0:yyyy-MM-dd}'", val);
                else
                    return string.Format("'{0:yyyy-MM-dd hh\\:mm\\:ss\\:fff}'", val);
            }
            else if (val is string)
                return "'" + val + "'";
            else
                return string.Format(CultureInfo.InvariantCulture, "{0}", val);
        }

        Regex _namedBookmarks = new Regex(@"(?<newline>\n)|PRINT(?<name>[^\r\n]+)\r?\n", RegexOptions.IgnoreCase | RegexOptions.Multiline);
        public List<KeyValuePair<string, int>> ParseNamedBookmarks(string script)
        {
            MatchCollection matches = _namedBookmarks.Matches(script);
            List<KeyValuePair<string, int>> list = new List<KeyValuePair<string, int>>();
            int line = 0;
            foreach (Match m in matches)
            {
                string newline = m.Groups["newline"].Value;
                string name = m.Groups["name"].Value;
                if (newline != "")
                {
                    line += 1;
                    continue;
                }
                if (name != "")
                {
                    list.Add(new KeyValuePair<string, int>(name.Trim(), line));
                    line += 1;
                    continue;
                }
            }
            return list;
        }

        protected virtual void GetScaleAndPrecision(out int scale, out int precision, IDataParameter param)
        {
            string d = string.Format(CultureInfo.InvariantCulture, "{0}", param.Value);
            int i = d.IndexOf('.') + 1;
            if (i == 0)
            {
                scale = d.Length;
                precision = 0;
            }
            else
            {
                scale = d.Length - 1;
                precision = d.Length - i;
            }
        }

        public string SimulateParamsUse(IDbDataParameter[] cmdParams)
        {
            string s = "";
            foreach (IDataParameter param in cmdParams)
            {
                if (s != "")
                    s += ", ";
                s += param.ParameterName + "=" + param.ParameterName;
            }
            if (s != "")
                s = "set " + s;
            string d = "";
            foreach (IDataParameter param in cmdParams)
            {
                if (d != "")
                    d += ", ";
                d += param.ParameterName + " " + FormatDbType(param);
            }
            if (d != "")
                d = "declare " + d;
            string v = "";
            foreach (IDataParameter param in cmdParams)
            {
                if (v != "")
                    v += ", ";
                v += param.ParameterName + "=" + FormatDbValue(param.Value);
            }
            if (v != "")
                v = "set " + v;
            return s + "\r\n-- " + d + "\r\n-- " + v;
        }

        DbType[] strings = new DbType[] { DbType.AnsiString, DbType.AnsiStringFixedLength, DbType.String, DbType.StringFixedLength };
        DbType[] dates = new DbType[] { DbType.Date, DbType.DateTime, DbType.DateTime2, DbType.DateTimeOffset, DbType.Time };
        DbType[] numfixed = new DbType[] { DbType.Currency, DbType.Double, DbType.Single, DbType.Int16, DbType.Int32, DbType.Int64, DbType.UInt16, DbType.UInt32, DbType.UInt64 };
        DbType[] numvar = new DbType[] { DbType.Decimal, DbType.VarNumeric };
        protected string FormatDbType(IDataParameter param)
        {
            DbType dbType = param.DbType;
            if (-1 != Array.IndexOf(strings, dbType))
            {
                string s = "" + (param.Value as string);
                return "varchar(" + s.Length + ")";
            }
            if (-1 != Array.IndexOf(dates, dbType))
            {
                return "datetime";
            }
            if (-1 != Array.IndexOf(numfixed, dbType))
            {
                return "int";
            }
            if (-1 != Array.IndexOf(numvar, dbType))
            {
                int scale;
                int precision;
                GetScaleAndPrecision(out scale, out precision, param);
                return "numeric(" + scale + "," + precision + ")";
            }
            else
                return "�" + dbType.ToString() + "?";
        }
    }
}
































